﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using System.Data.OleDb;
using System.IO;
using System.Net;
using System.Xml;
using Com.Toook.Common;
using MySql.Data.MySqlClient;
using Com.Toook.ParseHtml;
using Com.Toook.ParseHtml.Entity;

namespace Com.Toook.RssNews
{
    /// <summary>
    /// UpdateRssNews 的摘要说明
    /// </summary>
    public class UpdateRssNewsMySql
    {
        public UpdateRssNewsMySql()
        {
            // TODO: 在此处添加构造函数逻辑
            this.connectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
            //this.connection = //GetConfigContent.ConnectionStringNews;
            //    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            //    HttpContext.Current.Server.MapPath("~/App_Data/rss.mdb");
        }
        private string connectionString;

        public void UpdateAllSiteNews()
        {
            int sid = 0, showCount = 0, timeSpan = 0;
            string url = null;
            string type = "";
            string xmlFileName = "";
            string xmlSiteId = "";
            bool haveContent = false;
            bool haveImg = false;
            DateTime expiryTime = DateTime.Now;

            //string command = "select top 1 SiteId, Url, NewsCount, ExpiryTime, TimeSpan, haveContent, haveImg " +
            //    " from RssSites where IsValid=1 order by ExpiryTime";
            string command = "select SiteId, Url, NewsCount, ExpiryTime, TimeSpan, haveContent, haveImg, newsType, xmlFileName, xmlSiteId " +
                " from RssSites where IsValid=1 order by ExpiryTime asc limit 0,1";

            MySqlConnection con = new MySqlConnection(this.connectionString);
            MySqlCommand cmd = new MySqlCommand(command, con);
            MySqlDataReader reader = null;
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    sid = int.Parse(reader["SiteId"].ToString());
                    url = reader["Url"].ToString();
                    showCount = int.Parse(reader["NewsCount"].ToString());
                    DateTime.TryParse(reader["ExpiryTime"].ToString(), out expiryTime);
                    timeSpan = int.Parse(reader["TimeSpan"].ToString());
                    haveContent = "1" == reader["haveContent"].ToString();
                    haveImg = "1" == reader["haveImg"].ToString();
                    type = reader["newsType"].ToString();
                    xmlFileName = reader["xmlFileName"].ToString();
                    xmlSiteId = reader["xmlSiteId"].ToString();
                }
            }
            finally
            {
                MySqlHelperMy.CloseDbResource(con, reader);
            }

            try
            {
                if (expiryTime <= DateTime.Now)
                {
                    DownloadXml(sid, url, showCount, timeSpan, haveContent, haveImg, type);
                    string filePath = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "App_Data\\RssNewsContent\\" + xmlFileName;                   
                    GetNewsContent(filePath, xmlSiteId, sid);
                }
            }
            catch (Exception xmlDownEx)
            {
                string error = xmlDownEx.Message;
                error = error.Replace('\'', ' ');
                string errorCommand = "insert into errors(SiteId,Description,ErrorTime,Url) values(" + sid.ToString() + ",'" + error + "','" + DateTime.Now.ToString() + "','" + url + "')";
                cmd.CommandText = errorCommand;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                UpdateExpiryTime(sid, timeSpan * 2);////下载或插入出错时，更新时间，加2小时。 , connection
            }
        }
        /// <summary>
        /// 手动更新单个站点的新闻
        /// </summary>
        /// <param name="sid">站点ID</param>       
        /// <returns>返回值，正常为ok出错为异常原因</returns>
        public string UpdateOneSite(int sid)
        {
            string result = "ok";
            string command = "select Url, NewsCount, ExpiryTime, TimeSpan, haveContent, haveImg, newsType, xmlFileName, xmlSiteId " +
                " from RssSites where SiteId=" + sid.ToString();
            string url = "";
            int showCount = 40;
            DateTime expiryTime = DateTime.Now;
            int timeSpanMinute = 60;
            bool haveContent = false;
            bool haveImg = false;
            string xmlFileName = "";
            string xmlSiteId = "";
            string type = "";
            MySqlConnection con = MySqlHelperMy.GetConnection();
            MySqlCommand cmd = new MySqlCommand(command, con);
            MySqlDataReader reader = null;
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    url = reader["Url"].ToString();
                    showCount = int.Parse(reader["NewsCount"].ToString());
                    DateTime.TryParse(reader["ExpiryTime"].ToString(), out expiryTime);
                    timeSpanMinute = int.Parse(reader["TimeSpan"].ToString());
                    haveContent = "1" == reader["haveContent"].ToString();
                    haveImg = "1" == reader["haveImg"].ToString();
                    type = reader["newsType"].ToString();
                    xmlFileName = reader["xmlFileName"].ToString();
                    xmlSiteId = reader["xmlSiteId"].ToString();
                }
                else
                {
                    return "此SiteId没有对应的记录";
                }
            }
            finally
            {
                MySqlHelperMy.CloseDbResource(con, reader);
            }
            try
            {
                DownloadXml(sid, url, showCount, timeSpanMinute, haveContent, haveImg, type);
                string filePath = System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "App_Data\\RssNewsContent\\" + xmlFileName;
                //string filePath = HttpContext.Current.Server.MapPath("~\\App_Data\\RssNewsContent\\") + xmlFileName;
                GetNewsContent(filePath, xmlSiteId, sid);
            }
            catch (Exception e)
            {
                result = e.Message;
            }
            return result;
        }
        protected void DownloadXml(int sid, string url, int showCount, int timeSpan, bool haveContent, bool haveImg, string type)//, string connection
        {
            byte[] rssXml = new WebClient().DownloadData(url);
            XmlDocument doc = new XmlDocument();
            doc.Load(new MemoryStream(rssXml));
            XmlNodeList items = doc.SelectNodes("rss/channel/item");
            string title = null, link = null, pubDate = null, content = null, img = null;
            int sameCount = 0, insertCount = showCount;
            //int maxNumber = GetMaxInsertNumber();//取得RssNews中最大的InsertNumber值，即最后插入的值connection\
            //int addNumber = 1;//id增加值
            foreach (XmlNode item in items)
            {
                XmlNode linkNode = item.SelectSingleNode("link");
                if (linkNode == null)
                {
                    linkNode = item.SelectSingleNode("guid");
                    if (linkNode == null)
                    {
                        LogHelper.Log.Error("RSS没有链接地址link/guid" + url);
                        continue;
                    }
                    link = linkNode.InnerText;
                }
                else
                {
                    link = linkNode.InnerText;
                }
                if (!link.StartsWith("http", true, null))//不是有效链接
                {
                    continue;
                }
                string haveCmd = "SELECT count(link) AS linkCount FROM RssNews WHERE siteId=" + sid.ToString() + " and link=@link ";
                MySqlConnection cnnhave = MySqlHelperMy.GetConnection();
                MySqlCommand cmdhave = new MySqlCommand(haveCmd, cnnhave);
                cmdhave.Parameters.Add("@link", MySqlDbType.String, 200).Value = link;
                int counter = 0;
                try
                {
                    cnnhave.Open();
                    int.TryParse(cmdhave.ExecuteScalar().ToString(), out counter);
                }
                catch (Exception exh)
                {
                    LogHelper.Log.Error("获取链接数失败. link=" + link, exh);
                }
                finally
                {
                    cnnhave.Close();
                }                
                
                if (counter > 0)
                {
                    if (++sameCount > 3) //如果连续三个都已经有，跳出循环，更新站点时间。
                        break;
                    else
                        continue;
                }
                else
                {
                    try
                    {
                        title = item.SelectSingleNode("title").InnerText.Replace("&amp;", "&").Replace("&nbsp;", " ").Replace("nbsp;", "");
                        if (url.Contains("xinhuanet"))
                        {
                            //1、可以这样 pubDate = ParsePubDate(item.ChildNodes[3].Value, url);
                            //2、也可以：
                            XmlNodeList nodes = item.ChildNodes;
                            foreach (XmlNode node in nodes)
                            {
                                if (node.NodeType == XmlNodeType.Text)
                                {
                                    pubDate = ParsePubDate(node.Value, url);
                                    break;
                                }
                            }
                        }
                        else
                        {
                            pubDate = ParsePubDate(item.SelectSingleNode("pubDate").InnerText, url);
                        }
                        if (haveContent)
                        {
                            content = item.SelectSingleNode("description").InnerText;
                        }
                        //if (haveImg)
                        //{
                        //    img = item.SelectSingleNode("").InnerText;
                        //}
                    }
                    catch (Exception nodeEx)
                    {
                        LogHelper.Log.Error("获取title/pubDate/desc失败。url=" + url + nodeEx);
                    }

                    //int num = (maxNumber + insertCount--);
                    sameCount = 0;//有新的，将 sameCount 重置为零

                    MySqlConnection con = MySqlHelperMy.GetConnection();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = con;
                   
                    string otherColumn = "";
                    string otherParam = "";
                    string updateColumn = "";
                    int isGetContent = 0;
                    if (!string.IsNullOrEmpty(content))
                    {
                        otherColumn = ", content";
                        otherParam = ", @content";
                        updateColumn = ",content=@content";
                        cmd.Parameters.Add("@content", MySqlDbType.String).Value = content;
                        isGetContent = 1;
                    }
                    if (!string.IsNullOrEmpty(img))
                    {
                        otherColumn += ", img";
                        otherParam += ", @img";
                        updateColumn += ",img=@img";
                        cmd.Parameters.Add("@img", MySqlDbType.String, 200).Value = img;
                    }
                    //int id = maxNumber + addNumber++;
                    int updateId = GetUpdateId(sid, showCount);
                    string command = "";
                    if (updateId > 0)//获取到的更新id大于0，更新此id的记录
                    {
                        command = "update RssNews set title=@title, link=@link,pubDate=@pubDate, isGetContent=@isGetContent" + updateColumn + " where id=@id ";
                    }
                    else//等于0的直接插入
                    {
                        command = "insert into RssNews(title,link,pubDate,siteId,haveContent, newsType, isGetContent" + otherColumn + ") " +
                        " values(@title,@link,@pubDate,@siteId,@haveContent, @newsType, @isGetContent" + otherParam + ")";
                    }
                    //string insert = "insert into RssNews(id, title,link,pubDate,siteId,haveContent, type" + otherColumn + ") " +
                    //    " values(@id, @title,@link,@pubDate,@siteId,@haveContent, @type" + otherParam + ")";
                    cmd.CommandText = command;

                    cmd.Parameters.Add("@title", MySqlDbType.String, 30).Value = title;
                    cmd.Parameters.Add("@link", MySqlDbType.String, 200).Value = link;
                    cmd.Parameters.Add("@pubDate", MySqlDbType.String, 20).Value = pubDate;
                    cmd.Parameters.Add("@siteId", MySqlDbType.Int32).Value = sid;
                    cmd.Parameters.Add("@haveContent", MySqlDbType.Int32).Value = haveContent ? 1 : 0;
                    cmd.Parameters.Add("@newsType", MySqlDbType.Int32).Value = type; //int.Parse(type);
                    cmd.Parameters.Add("@isGetContent", MySqlDbType.Int32).Value = isGetContent;
                    cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = updateId;

                    try
                    {
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception exx)
                    {
                        //insertCount++;
                        LogHelper.Log.Error(exx);
                    }
                    finally
                    {
                        con.Close();
                    }
                    if (insertCount == 0)//加够 showCount 条后，跳出。
                    {
                        break;
                    }
                }
            }

            UpdateExpiryTime(sid, timeSpan);//, connection
        }
        /// <summary>
        /// 将RSS源的日期处理成某月某日
        /// </summary>
        /// <param name="pubDate">RSS的日期</param>
        /// <returns>月日</returns>
        protected string ParsePubDate(string pubDate, string url)
        {
            string result;
            DateTime date = DateTime.Now;
            if(!DateTime.TryParse(pubDate, out date))
            {                
                LogHelper.Log.Warn("解析日期出错。pubDate=" + pubDate + ", Rss地址=" + url);
            }
            result = date.ToString(ConfigurationManager.AppSettings["NewsPubDateFormat"]);//"yyyy年MM月dd日"
            return result;
        }
        
        /// <summary>
        /// 查询RssNews表中指定分类的id。表中只保存maxCount条记录，超过后循环更新，不足时直接插入。      
        /// </summary>
        /// <param name="siteId">站点分类id</param>
        /// <param name="maxCount">最多保存多少条记录</param>
        /// <returns>1、返回0表示此分类记录没有达到最大，直接插入数据  2、返回大于0的数表示Id,更新此id的记录</returns>
        protected int GetUpdateId(int siteId, int maxCount)
        {
            int resultId = 0;
            string sqlCount = "select count(*) from RssNews where siteId=@siteId ";//查询分类记录总数
            string sqlId = "select id from RssNews where siteId=@siteId order by createTime asc limit 0, 1 ";//查询分类记录中最早添加的id
            MySqlConnection con = MySqlHelperMy.GetConnection();
            MySqlCommand cmd = new MySqlCommand(sqlCount, con);
            cmd.Parameters.Add("@siteId", DbType.Int32).Value = siteId;
            int number = 0;
            try
            {
                con.Open();
                int.TryParse(cmd.ExecuteScalar().ToString(), out number);
                if (number >= maxCount)//如果等于/超过maxCount，返回最早添加的id,供循环更新
                {
                    cmd.CommandText = sqlId;
                    object id = cmd.ExecuteScalar();
                    if (id != null)
                    {
                        int.TryParse(id.ToString(), out number);
                    }
                }
                else//如果刚开始采集，没有达到最大条数时，返回0，直接插入数据
                {
                    resultId = 0;
                }              
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("获取RssNews 表中最早更新id/分类记录数失败。siteId=" + siteId, ex);
            }
            finally
            {
                con.Close();
            }

            return resultId;
        }
        /// <summary>      
        /// 查询RssNews 表中最大的id
        /// </summary>
        /// <returns></returns>
        protected int GetMaxInsertNumber()
        {
            string command = "select id from RssNews order by id desc limit 0, 1 ";
            MySqlConnection con = MySqlHelperMy.GetConnection();
            MySqlCommand cmd = new MySqlCommand(command, con);
            int number = 0;
            try
            {
                con.Open();
                object id = cmd.ExecuteScalar();
                if (id != null)
                {
                    int.TryParse(id.ToString(), out number);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("获取RssNews 表中最大的id失败", ex);
            }
            finally
            {
                con.Close();
            }
            return number;
        }

        protected void UpdateExpiryTime(int sid, int timeSpan)
        {
            MySqlConnection con = MySqlHelperMy.GetConnection();
            string command = "update RssSites set ExpiryTime=@ExpiryTime where SiteId=@SiteId";
            MySqlCommand cmd = new MySqlCommand(command, con);
            cmd.Parameters.Add("@ExpiryTime", DbType.DateTime).Value = DateTime.Now.AddMinutes(timeSpan);
            cmd.Parameters.Add("@SiteId", DbType.Int32).Value = sid;
            try
            {
                con.Open();
                int count = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogHelper.Log.Error("update RssSites ExpiryTime失败。sid=" + sid.ToString() , ex);
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// 获取内容
        /// </summary>
        /// <param name="xmlFilePath"></param>
        /// <param name="xmlSiteId">xml中的siteId 如 sina_world</param>
        /// <param name="siteId">RssSites中的分类SiteId 如 101</param>
        protected void GetNewsContent(string xmlFilePath, string xmlSiteId, int siteId)
        {            
            //GetKindResultFromHtml htmlKind = new GetKindResultFromHtml(xmlFilePath, siteId);
            //htmlKind.GetKindPageResults();
            CreateContentByXml createContent = new CreateContentByXml(xmlFilePath);
            DetailRoadMap roadMap = createContent.GetDetailRoadMap(xmlSiteId);
            GetContentResultFromHtml html = new GetContentResultFromHtml(roadMap);
            html.GetRssNewsContent(siteId);
        }
    }
}